package com.dimples.strategy.service;

import com.dimples.constant.SQLConstant;
import com.dimples.strategy.BuildTableStrategyService;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;
import java.util.Optional;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * @author zhongyj <1126834403@qq.com><br/>
 * @date 2021/2/20
 */
@Slf4j
@Service
public class MysqlFileBuildTableServiceImpl implements BuildTableStrategyService {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public MysqlFileBuildTableServiceImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public List<String> create(MultipartFile file, String sql) {
        List<String> msg = CollUtil.newArrayList();
        if (ObjectUtil.isNotEmpty(file)) {
            log.info("========================== 开始解析SQL脚本文件 ================================");
            String filename = FileUtil.getTmpDirPath() + file.getOriginalFilename();
            try {
                FileUtil.writeFromStream(file.getInputStream(), filename);
                List<String> lines = FileUtil.readUtf8Lines(filename);
                List<String> createSql = CollUtil.newArrayList();
                List<String> insertSql = CollUtil.newArrayList();
                String sqlExe = "";
                String tableName = "";
                // 是否为建表语句标记
                boolean flag = false;
                for (String line : lines) {
                    // 处理数据库差异
                    for (String key : SQLConstant.mysqlToCacheReplace.keySet()) {
                        if (StrUtil.containsIgnoreCase(line, key)) {
                            line = StrUtil.replace(line, key, SQLConstant.mysqlToCacheReplace.get(key));
                        }
                    }
                    // 建表语句开始
                    if (StrUtil.containsIgnoreCase(line, SQLConstant.SQL_CREATE_TABLE)) {
                        flag = true;
                        // 获取并处理表名
                        tableName = Optional
                                .of(StrUtil.subAfter(line, SQLConstant.SQL_CREATE_TABLE.toLowerCase(), false))
                                .orElse(StrUtil.subAfter(line, SQLConstant.SQL_CREATE_TABLE.toLowerCase(), false));
                        createSql.add(StrUtil.replaceIgnoreCase(line, tableName, SQLConstant.SCHEMA_NAME + "." + tableName));
                        continue;
                    }
                    // 建表语句结束
                    if (StrUtil.containsIgnoreCase(line, SQLConstant.MYSQL_ENGINE)) {
                        flag = false;
                        createSql.add(StrUtil.subBefore(line, SQLConstant.MYSQL_ENGINE, false));
                        sqlExe = CollUtil.join(createSql, "\n");
                        createSql.clear();
                    }
                    // 建表字段
                    if (flag) {
                        String subBefore = StrUtil.subBefore(line, SQLConstant.MYSQL_COMMENT, false);
                        if (StrUtil.endWith(line, ",") && !StrUtil.endWith(subBefore, ",")) {
                            subBefore += ",";
                        }
                        createSql.add(StrUtil.removeAll(subBefore, "auto_increment"));
                    }
                    // 建表语句完成
                    if (StrUtil.isNotBlank(sqlExe)) {
                        try {
                            log.info("\n================================ 开始创建表【{}】===================================\n" +
                                            "{}\n====================================================================="
                                    , StrUtil.removeSuffix(tableName, "("), sqlExe);
                            this.jdbcTemplate.execute(sqlExe);
                        } catch (Exception e) {
                            log.info("创建表【{}】失败，信息为：" + e.getMessage(), tableName);
                            msg.add(StrUtil.format("创建表【{}】失败，信息为：【{}】", tableName, e.getMessage()));
                            sqlExe = "";
                            continue;
                        }
                        sqlExe = "";
                        tableName = "";
                    }
                    if (StrUtil.containsIgnoreCase(line, SQLConstant.SQL_INSERT_INTO)) {
                        String name = Optional
                                .ofNullable(StrUtil.subBetween(line, SQLConstant.SQL_INSERT_INTO, SQLConstant.SQL_VALUES))
                                .orElse(StrUtil.subBetween(line, SQLConstant.SQL_INSERT_INTO.toLowerCase(), SQLConstant.SQL_VALUES.toLowerCase()));
                        insertSql.add(StrUtil.replace(line, name, SQLConstant.SCHEMA_NAME + "." + name));
                    }
                }

                for (String insert : insertSql) {
                    insert = StrUtil.removeSuffix(insert, ";");
                    try {
                        log.info("执行插入语句：【{}】", insert);
                        this.jdbcTemplate.update(insert);
                    } catch (Exception e) {
                        log.info(e.getMessage());
                        msg.add(StrUtil.format("执行插入语句【{}】失败，信息为：【】", insert, e.getMessage()));
                    }
                }
            } catch (IOException e) {
                log.info("文件解析错误: " + e.getMessage());
            } finally {
                FileUtil.del(filename);
            }
        }
        if (CollUtil.isEmpty(msg)) {
            msg.add("成功");
        }
        return msg;
    }
}
